package com.javaweb.dao.impl;

import com.javaweb.dao.RoleDao;
import com.javaweb.model.Permission;
import com.javaweb.model.Role;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
@Slf4j
public class RoleDaoImpl implements RoleDao {
    @Autowired
    private Connection conn ;
    @Override
    public Integer insert(Role role) {
        String sql = "INSERT INTO tb_role (role_id, name) VALUES (?, ?)";
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)) {
            ps.setInt(1, role.getRoleId());
            ps.setString(2, role.getName());
            int affectedRows = ps.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Creating user failed, no rows affected.");
            }




        } catch (SQLException e) {
            e.printStackTrace();

        }
        return null;
    }

    @Override
    public Integer insertRolePerm(Role role, Permission perm) {
        String sql = "INSERT INTO tb_role_per (role_id, per_id) VALUES (?, ?)";
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)) {

            ps.setInt(1, role.getRoleId());
            ps.setInt(2, perm.getPerId());

            int affectedRows = ps.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Inserting role permission failed, no rows affected.");
            }

            return affectedRows;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    // 根据用户名查询角色（这通常涉及多表查询）
    @Override
    public List<Role> selectRolesByUserName(String username) {
        List<Role> roles = new ArrayList<>();
        String sql = "SELECT r.* FROM tb_role r " +
                "JOIN tb_user_role ur ON r.role_id = ur.role_id " +
                "JOIN tb_user u ON ur.user_id = u.user_id " +
                "WHERE u.name = ?";
        try (PreparedStatement ps = this.conn.prepareStatement(sql)) {
            ps.setString(1, username);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Role role = new Role();
                    role.setRoleId(rs.getInt("role_id"));
                    role.setName(rs.getString("name"));

                    roles.add(role);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return roles;
    }


    @Override
    public List<Role> selectRolesByUserId(int userId) {
        List<Role> roles = new ArrayList<>();
        String sql = "SELECT r.* FROM tb_role r JOIN tb_user_role ur ON r.role_id = ur.role_id WHERE ur.user_id = ?";
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)) {

            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Role role = new Role();
                role.setRoleId(rs.getInt("id"));
                role.setName(rs.getString("role_name"));
                roles.add(role);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return roles;
    }

    // 根据角色ID删除角色
    @Override
    public void deleteByRoleId(int roleId) {
        String sql = "DELETE FROM tb_role WHERE role_id = ?";
        try (
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, roleId);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除用户与角色的关联
    @Override
    public void deleteUserRole(int userId, int roleId) {
        String sql = "DELETE FROM tb_user_role WHERE user_id = ? AND role_id = ?";
        try (
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, userId);
            ps.setInt(2, roleId);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}